BEGIN;
CREATE TABLE "tongues" (
    "cd_tongue" text NOT NULL PRIMARY KEY,
    "ds_tongue" text NOT NULL
)
;
CREATE TABLE "routes" (
    "id" serial NOT NULL PRIMARY KEY,
    "departure_addr" text NOT NULL,
    "departure_lat" numeric(8, 6) NOT NULL,
    "departure_lng" numeric(9, 6) NOT NULL,
    "arriva_addr" text NOT NULL,
    "arriva_lat" numeric(8, 6) NOT NULL,
    "arriva_lng" numeric(9, 6) NOT NULL,
    "route_lenght_mt" integer NOT NULL
)
;
CREATE TABLE "journeys" (
    "id" serial NOT NULL PRIMARY KEY,
    "departure_datetime" timestamp NOT NULL,
    "passengers" boolean NOT NULL,
    "luggages" boolean NOT NULL,
    "status" text NOT NULL, -- bisogna censire i valori: OPEN, CLOSE
    "id_route" integer NOT NULL REFERENCES "routes" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE "users" (
    "id" serial NOT NULL PRIMARY KEY,
    "openid" text NOT NULL UNIQUE,
    "username" text NOT NULL UNIQUE,
    "gender" text NOT NULL,
    "nationality" text NOT NULL,
    "birth_year" text NOT NULL,
    "photo" text, 
    "fl_on_line" boolean NOT NULL
)
;
CREATE TABLE "user_tongues" (
    "id_user" integer NOT NULL REFERENCES "users" ("id") DEFERRABLE INITIALLY DEFERRED,
    "cd_tongue" text NOT NULL REFERENCES "tongues" ("cd_tongue") DEFERRABLE INITIALLY DEFERRED,
    "priority" integer NOT NULL,
    --UNIQUE(id_user, priority),
    UNIQUE(id_user, cd_tongue),
    CONSTRAINT user_tongues_pkey PRIMARY KEY (id_user, cd_tongue, priority)
)
;
CREATE TABLE "user_journeys" (
    "id_user" integer NOT NULL REFERENCES "users" ("id") DEFERRABLE INITIALLY DEFERRED,
    "id_journey" integer NOT NULL REFERENCES "journeys" ("id") DEFERRABLE INITIALLY DEFERRED,
    "fl_owner" boolean NOT NULL,
    CONSTRAINT user_journeys_pkey PRIMARY KEY (id_user, id_journey)
)
;
CREATE TABLE "contacts" (
    "id_user" integer NOT NULL REFERENCES "users" ("id") DEFERRABLE INITIALLY DEFERRED,
    "cd_contact" text NOT NULL,
    "ds_contact" text NOT NULL,
    "priority" integer NOT NULL,
    UNIQUE(id_user, priority),
    CONSTRAINT contacts_pkey PRIMARY KEY (id_user, cd_contact, priority)
)
;
CREATE TABLE "messages" (
    "id" serial NOT NULL PRIMARY KEY,
    "title" text NOT NULL,
    "body" text NOT NULL,
    "type" text, -- (non usato) bisogna censire i valori
    "status" text, -- (non usato) bisogna censire i valori: UNREAD, READ, DELETE
    "datetime" timestamp NOT NULL,
    "id_journey" integer NOT NULL REFERENCES "journeys" ("id") DEFERRABLE INITIALLY DEFERRED,
    "id_sender" integer NOT NULL REFERENCES "users" ("id") DEFERRABLE INITIALLY DEFERRED,
    "id_recipient" integer NOT NULL REFERENCES "users" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE INDEX "journeys_id_route" ON "journeys" ("id_route");
CREATE INDEX "user_tongues_id_user" ON "user_tongues" ("id_user");
CREATE INDEX "user_tongues_cd_tongue" ON "user_tongues" ("cd_tongue");
CREATE INDEX "user_journeys_id_user" ON "user_journeys" ("id_user");
CREATE INDEX "user_journeys_id_journey" ON "user_journeys" ("id_journey");
CREATE INDEX "contacts_id_user" ON "contacts" ("id_user");
CREATE INDEX "contacts_cd_contact" ON "contacts" ("cd_contact");
CREATE INDEX "messages_id_sender" ON "messages" ("id_sender");
CREATE INDEX "messages_id_recipient" ON "messages" ("id_recipient");
CREATE INDEX "routes_departure_lat" ON "routes" ("departure_lat");
CREATE INDEX "routes_departure_lng" ON "routes" ("departure_lng");
CREATE INDEX "routes_arriva_lat" ON "routes" ("arriva_lat");
CREATE INDEX "routes_arriva_lng" ON "routes" ("arriva_lng");
COMMIT;
